<?php
//        PHPcongrats - Holiday and Birthday Mailer
//
//    Copyright (C) 2011  Matthias Nass
//
//    This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 3 of the License, or (at your option) any later version.
//
//    This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
//
//    You should have received a copy of the GNU General Public License along with this program; if not, see <http://www.gnu.org/licenses/>.

// ACTIONSCRIPT v0.1 TO BE EXCECUTED BY CRON


//Setting for email header
$header = 'From: webmaster@example.com' . "\r\n" .
    	  'Reply-To: webmaster@example.com' . "\r\n" .
    	  'X-Mailer: PHP/' . phpversion();

//Setting for Logfile / you have to create the Logfile
$logFile = "/var/www/php/PHPCongratsLog.txt";
$fh = fopen($logFile, 'a') or die("PHPCongrats can´t open Log-File to write");

//Settings for Database
$link = mysql_connect("localhost", "root", "lA7zLMxV");
if(!$link){fwrite($fh, "Cannot connect to Database");};
mysql_select_db("phpcongrats");

//Get current Date
//testdate
$date = getdate();
$current_date_str = sprintf('%04d-%02d-%02d', $date['year'], $date['mon'], $date['mday']);
fwrite($fh, "DAY:".$current_date_str."\n");

// BIRTHDAYS
// Select Persons who have Birthday today
$query = "SELECT * FROM persons WHERE MONTH(birthdate)=".$date['mon']." AND DAYOFMONTH(birthdate)=".$date['mday']." AND birthdaycongrats=1;";
$result = mysql_query($query, $link);

// Get Texts for Birthday
$query = "SELECT * FROM text WHERE holidays_id=1;";
$result_text = mysql_query($query, $link);
$array_text_ids = array();

while ($row_text = mysql_fetch_row($result_text) ){
     array_push($array_text_ids,$row_text[0]);
}

while ($row = mysql_fetch_row($result)){
     
     // Nach Reihenfolge
     $query_last = "SELECT MAX(text_count) FROM personholitextlast WHERE persons_id=$row[0] AND holidays_id=1;";
     $result_last = mysql_query($query_last, $link);
     if($result_last != null){
          $count = mysql_fetch_field($result_lase,2);
          $stop = $count;
          $result_text=null;
          while($result_text==null && $stop<20){
               $stop++;               
               $query_text = "SELECT * FROM text WHERE holidays_id=1 AND count=$stop";
               $result_text_row = mysql_query($query_text,$link);
               
          }
     }else {
          $query_text = "SELECT *,MIN(count) FROM text WHERE holidays_id=1;";
          $result_text_row = mysql_query($query_text,$link);
          
     }
     $text_row = mysql_fetch_row($result_text_row);
     $text_id = $text_row[0];
     $query_ins_last = "INSERT INTO persholitextlast VALUES($row[0],1,$text_row[7]);";
     mysql_query($query_ins_last,$link);
     //Nach Zufall
     //$text_id = $array_text_ids[rand(0,count($array_text_ids)-1)];
     //$query_text = "SELECT * FROM text WHERE id=$text_id";
     //$result_text_row = mysql_query($query_text, $link);
     //$text_row = mysql_fetch_row($result_text_row);
     $text = $text_row[4];
     $birthdate_arr = explode("-",$row[7]);
     $text = str_replace ("|age|", $birthdate_arr[0]-$date['year'], $text);
     $text = str_replace ("|firstname|", $row[1], $text);
     $text = str_replace ("|lastname|", $row[2], $text);
     $text = str_replace ("|title|", $row[4], $text);
     $text = str_replace ("|city|", $row[5], $text);
     if ($row[5]=='m') {$gender="Herr";} else {$gender="Frau";};
     $text = str_replace ("|gender|", $gender, $text);
     $to = "matthiasnass3@googlemail.com";
     $subject = "Herzichen Glückwunsch zum Geburtstag!";
     $body = $text;
     if (mail($to, $subject, $body, $header)) {
          fwrite($fh, " BIRTHDAY Message send:"." PERSONID: ".$row[0]." TEXTID: ".$text_id."\n");;
     } else {
          fwrite($fh, " BIRTHDAY Message failed:"." PERSONID: ".$row[0]." TEXTID: ".$text_id."\n");;
     }
}

// Other Holidays
$query = "SELECT * FROM holidays MONTH(date)=".$date['mon']." AND DAYOFMONTH(date)=".$date['mday'].";";
$result_holidays = mysql_query($query, $link);
if ($result_holidays)  {
     while ($row_holidays = mysql_fetch_row($result_holidays)){
          $query = "SELECT * FROM text WHERE holidays_id=$row_holidays[0];";
          $result_text = mysql_query($query, $link);

          $array_text_ids = array();
           
          while ($row_text = mysql_fetch_row($result_text) ){
               array_push($array_text_ids,$row_text[0]);
          }
          if(count($array_text_ids)>0){
               $query = "SELECT * FROM congrats LEFT JOIN persons ON persons_id=id WHERE holidays_id=$row_holidays[0]";
               $result_persons = mysql_query($query, $link);
               while ($row_persons = mysql_fetch_row($result_persons)){
                    $birthdate_arr = explode("-",$row_persons[10]);
                    $text_id = $array_text_ids[rand(0,count($array_text_ids)-1)];
                    $query = "SELECT * FROM text WHERE id=$text_id";
                    $result_text_row = mysql_query($query, $link);
                    $text_row = mysql_fetch_row($result_text_row);
                    $text = $text_row[4];
                    $text = str_replace ("|age|", $birthdate_arr[0]-$date['year'], $text);
                    $text = str_replace ("|firstname|", $row_persons[4], $text);
                    $text = str_replace ("|lastname|", $row_persons[5], $text);
                    $text = str_replace ("|title|", $row_persons[6], $text);
                    $text = str_replace ("|city|", $row_persons[7], $text);
                    if ($row_persons[8]=='m') {$gender="Herr";} else {$gender="Frau";};
                    $text = str_replace ("|gender|", $gender, $text);
                    $to = "matthiasnass3@googlemail.com";
                    $subject = "Herzichen Glückwunsch zum Geburtstag!";
                    $body = $text;
                    if (mail($to, $subject, $body, $header)) {
                         fwrite($fh, " HOLIDAYID:".$row_holidays[0]." Message send:"." PERSONID: ".$row_persons[0]." TEXTID: ".$text_id."\n");
                    } else {
                         fwrite($fh, " HOLIDAYID:".$row_holidays[0]." Message failed:"." PERSONID: ".$row_persons[0]." TEXTID: ".$text_id."\n");
                    }
               }
          }
     }
} else {
     fwrite($fh, " No holiday today \n");
}
mysql_close($link);
fclose($fh);
?>